﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace MyDMS.BusinessLayer.DataLayer
{
    /// <summary>
    /// Data access layer class for Department
    /// </summary>
    class QuerySql : DataLayerBase
    {
        #region Constructor

        /// <summary>
        /// Class constructor
        /// </summary>
        public QuerySql()
        {
            // Nothing for now.
        }

        #endregion

        #region Public Methods

        public bool ExecuteSP(string StoredProcedure, params object[] sqlParameters)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = StoredProcedure;
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                sqlCommand.Parameters.AddRange(sqlParameters);

                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Query::ExecuteSP::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }
        }

        public bool ExecuteNonQuery(string sqlQuery)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = sqlQuery;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Query::ExecuteSP::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }
        }

        public bool ExecuteQuery(string sqlQuery, out object dataObject, int dataObjectType)
        {
            dataObject = null;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = sqlQuery;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                MainConnection.Open();

                if (dataObjectType == 1)
                {
                    DataSet dsData = new DataSet();
                    new SqlDataAdapter(sqlCommand).Fill(dsData);
                    dataObject = dsData;
                }
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Query::ExecuteSP::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }
        }
        #endregion
    }
}
